package Util;

import EncapsulatingObjects.Trip;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class TripDatabaseUtil {
    public Connection getConnection() {
        Connection conn = null;
        //加载一个驱动器
        try {
            Class.forName("org.apache.derby.jdbc.ClientDriver");
            //定义一个链接
            String url = "jdbc:derby://localhost:1527/NIIT";
            String user = "root";
            String password = "root";
            conn = DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return conn;
    }

    public ArrayList<String> getColumn() {
        ArrayList<String> columns = new ArrayList<>();
        Connection conn = getConnection();
        Statement stmt = null;
        String sql = "select * from tripdata";
        try {
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            ResultSetMetaData md = rs.getMetaData();

            int column = md.getColumnCount();

            //将上方得到的数据库中的表的列名依次存储在动态数组columnNames中
            for (int i = 1; i <= column; i++) {
                columns.add(md.getColumnName(i));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return columns;
    }

    public void createTable() throws SQLException {
        Connection conn = getConnection();
        String str1 = "create table tripdata ( " +
                "id int generated always as identity," +
                "tripname varchar(100) not null," +
                "sPosition varchar(100) not null, " +
                "ePosition varchar(100) not null, " +
                "sDate date not null," +
                "eDate date not null," +
                "hotel varchar(100)," +
                "comment varchar(500)," +
                "money double not null, " +
                "primary key (id))";
        Statement stmt1 = null;
        stmt1 = conn.createStatement();
        stmt1.execute(str1);
        stmt1.close();
    }

    public List<Trip> quaryAll() {
        List<Trip> trips = new ArrayList<>();
        try {
            Connection conn = getConnection();
            PreparedStatement ps = conn.prepareStatement("select * from tripdata");
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Trip t = new Trip();
                t.setId(rs.getInt("id"));
                t.setName(rs.getString("tripname"));
                t.setMoney(rs.getDouble("money"));
                t.setsPosition(rs.getString("sposition"));
                t.setePosition(rs.getString("eposition"));
                t.setsDate(rs.getDate("sDate"));
                t.seteDate(rs.getDate("eDate"));
                t.setDoWhat(rs.getString("comment"));
                t.setHotelNamex(rs.getString("hotel"));
                trips.add(t);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return trips;
    }

    public List<Trip> quaryAll(int[] tripsId) {
        List<Trip> trips = new ArrayList<>();
        try {
            if(tripsId.length == 0)
                return trips;
            Connection conn = getConnection();
            PreparedStatement ps = conn.prepareStatement("select * from tripdata");
            ResultSet rs = ps.executeQuery();
            int j = 0;
            for (int i = 1; rs.next() && j< tripsId.length; i++) {
                if (rs.getInt(1) == tripsId[j]) {
                    Trip t = new Trip();
                    t.setId(rs.getInt("id"));
                    t.setName(rs.getString("tripname"));
                    t.setMoney(rs.getDouble("money"));
                    t.setsPosition(rs.getString("sposition"));
                    t.setePosition(rs.getString("eposition"));
                    t.setsDate(rs.getDate("sDate"));
                    t.seteDate(rs.getDate("eDate"));
                    t.setDoWhat(rs.getString("comment"));
                    t.setHotelNamex(rs.getString("hotel"));
                    trips.add(t);
                    j++;
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return trips;
    }

    public void updateTrip(Trip trip) {
        try {
            Connection conn = getConnection();
            String sql = "update tripdata set tripname=?,sPosition=?,ePosition=?, sDate=?, eDate=?, comment=?, hotel=?,money=? where id=?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, trip.getName());
            ps.setString(2, trip.getsPosition());
            ps.setString(3, trip.getePosition());
            ps.setDate(4, trip.getsDate());
            ps.setDate(5, trip.geteDate());
            ps.setString(6, trip.getDoWhat());
            ps.setString(7, trip.getHotelNamex());
            ps.setDouble(8, trip.getMoney());
            ps.setInt(9, trip.getId());
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void delete(int i) {
        //获取连接
        Connection conn = getConnection();
        //准备sql
        String sql = "delete from tripdata where id=?";
        //获取PreparedStatement
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            //填充占位符
            ps.setInt(1, i);
            //执行sql
            ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }

    public boolean insertInfo(Trip trip) throws SQLException {
        Connection conn = getConnection();
        PreparedStatement ps = null;
        ps = conn.prepareStatement("Insert into tripdata(tripname,sPosition,ePosition," +
                "sDate,eDate,comment,hotel,money)" + "Values (?,?,?,?,?,?,?,?)");
        ps.setString(1, trip.getName());
        ps.setString(2, trip.getsPosition());
        ps.setString(3, trip.getePosition());
        ps.setDate(4, trip.getsDate());
        ps.setDate(5, trip.geteDate());
        ps.setString(6, trip.getDoWhat());
        ps.setString(7, trip.getHotelNamex());
        ps.setDouble(8, trip.getMoney());
        ps.executeUpdate();
        ps.close();
        return true;

    }
}